<?php

/*
 * This file will perform the ajax for editing a department.
 * This is being used by admEditDept.php.
 * 
 * Created by: Peter Agno Jr.
 * Date created: November 23, 2011
 * 
 * In parameters: deptId and deptName
 * Out parameters: flag and msg about the success of editing of department
 */

//********************************************************************************************************
/*
 * Define the functions in here
 */
    // Start - Check if department has no ongoing route
    function noOngoingRoute($deptId) {
        $query = 
        "
            SELECT *
            FROM

                -- You now have departments that have ongoing route
                (SELECT DP.departmentId as departmentId
                FROM RouteStep as RS, RouteStep_Dept_Pos as RSDP, Department_Position as DP,

                    -- You now have filtered the ongoing routes
                    (SELECT RS.routeId as routeId
                    FROM RouteStep as RS, 

                        -- Filter routesteps
                        (SELECT stepId
                        FROM Log
                        GROUP BY stepId) as OngoingSteps

                    WHERE OngoingSteps.stepId = RS.stepId
                    GROUP BY RS.routeId) as OngoingRoutes

                WHERE OngoingRoutes.routeId = RS.routeId AND RS.stepId = RSDP.stepId AND RSDP.deptPosId = DP.deptPosId
                GROUP BY DP.departmentId) as OngoingDepts
            
            WHERE departmentId = '$deptId'
        ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_fetch_array($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check if department has an ongoing route
    
    // Start - Check if department has no data in Department_Position
    function noDeptPos($deptId) {
        $query = 
        "
            SELECT * 
            FROM Department_Position 
            WHERE departmentId = '$deptId'
        ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_fetch_array($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check if department has data in Department_Position
    
    // Start - Check if department has connection with any user
    function noConnectedUsers($deptId) {
        $query = 
        "
            SELECT *
            FROM
            
                -- You now have the departments that has connection with users
                (SELECT DP.departmentId as departmentId
                FROM Log as L, Account as A, Account_Dept_Pos as ADP, Department_Position as DP
                WHERE L.userId = A.userId AND A.userId = ADP.userId AND ADP.deptPosId = DP.deptPosId 
                GROUP BY DP.departmentId) as ConnectUsrs
            
            WHERE departmentId = '$deptId'
        ";
        
        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_fetch_array($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check if department has connection with any user
    
    // Start - Check if department name is existing
    function nameNotExist($deptName) {
        $query =
            "
                SELECT * 
                FROM Department
                WHERE departmentName = '$deptName';
            ";

        $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
        
        if ( mysql_fetch_array($result) == 0 ) {
            return true;
        }
        else {
            return false;
        }
    } // End - Check if department name is existing
    
    // Start - Edit data in Department
    function editDept($deptId, $deptName, $currentDateTime) {
        $query =
            "
                UPDATE Department 
                SET departmentName = '$deptName', departmentLastUpdated = '$currentDateTime'
                WHERE departmentId = '$deptId';
            ";

        mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
    } // End - Edit data in Department
    
//********************************************************************************************************

session_start();

// Start - Checker for those users who will just go to the page by typing directly in the url.
if ($_POST) {
    include('../includes/siteConfig.php');
    
    // connect to database
    $connect = mysql_connect($hostName,$rootName,$dBasePassword) or die ('Unable to connect!');
    mysql_select_db($dBaseName) or die ('Unable to select database!');
    
    // Get the data from post
    $deptId = $_POST['deptId'];
    $deptName = $_POST['deptName'];
    
    //GET THE CURRENT DATE TIME
    date_default_timezone_set('Asia/Singapore');
    $currentDateTime = date("Y-m-d H:i:s" , time());
    
    if ( noOngoingRoute($deptId) == true && noDeptPos($deptId) == true && noConnectedUsers($deptId) == true ) {
        // Allow edit department if:
        // - No ongoing route, meaning no current route is using this department
        // - no data yet in Department_Position table
        // - and no department connected with any users
        
        // Check if department name is existing
//        if ( nameNotExist($deptName) == true ) {
            editDept($deptId, $deptName, $currentDateTime);
        
            $jsondata = array();
            $jsondata['msg'] = "Department name of ".$deptId." was successfully updated.";
            $jsondata['flag'] = "success";
            $feed[] = $jsondata;
            echo json_encode($feed);
//        }
//        else {
//            $jsondata = array();
//            $jsondata['msg'] = "Error : Department name already exists.";
//            $jsondata['flag'] = "error";
//            $feed[] = $jsondata;
//            echo json_encode($feed);
//        }
    }
    else {
        $jsondata = array();
        $jsondata['msg'] = "Error : Ajax in editing the department was not successful.";
        $jsondata['flag'] = "error";
        $feed[] = $jsondata;
        echo json_encode($feed);
    }
    
    mysql_close($connect);
}   // End - Checker for those users who will just go to the page by typing directly in the url.
else {
    echo "You are not authorized to view this page. This incident will be reported immediately.";
}
?>
